package com.swordget.service.facade.impl;


import com.alibaba.fastjson.JSONObject;
import com.swordget.commons.ColumnDesignCompareConstant;
import com.swordget.dao.TableDefinitionDao;
import com.swordget.entity.ColumnDesign;
import com.swordget.entity.TableDesign;
import com.swordget.entity.bo.ColMetadata;
import com.swordget.service.*;
import com.swordget.service.facade.TableManagerFacadeService;
import com.swordget.util.FreemarkerUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.SQLException;
import java.util.*;

@Service
@Slf4j
public class TableManagerFacadeServiceImpl implements TableManagerFacadeService {

    @Autowired
    private TableManagerService tableManagerService;

    @Autowired
    private PanelService panelService;

    @Autowired
    private ColumnDesignCompareService columnDesignCompareService;

    @Autowired
    private TableDefinitionDao tableDefinitionDao;

    private String TPL_TYPE = "oracle";

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private TableDefinitionV2Service tableDefinitionV2Service;

    @Autowired
    private VerifyColumnService verifyColumnService;


    @Override
    public boolean makeTable(TableDesign tableDesign, List<ColumnDesign> columns, String loginId) throws SQLException {
        //1.创建表
        tableManagerService.makeTable(tableDesign, columns, loginId);
        //2.关联到指定面板
        return panelService.addPanelTable(tableDesign.getTableId(), "1");
    }

    @Override
    public boolean editTable(TableDesign tableDesign, String loginId) throws SQLException {
        // 修改注释
        return tableManagerService.editTable(tableDesign, loginId);
    }

    @Override
    @Transactional
    public boolean modifyTable(TableDesign tableDesign, List<ColumnDesign> columnDesigns, String loginId) {
        //0.前置处理
        verifyColumnService.verifyColumn(columnDesigns);
        //1.比较新老属性集，区分出新增 删除  修改 注释
        List<ColumnDesign> dbColumnDesigns = tableDefinitionDao.getColumnDesigns(tableDesign.getTableId(), false, false);
        Map<String, List<ColMetadata>> compareResult = columnDesignCompareService.compare(columnDesigns, dbColumnDesigns);
        List<ColMetadata> addCols = compareResult.get(ColumnDesignCompareConstant.TYPE_ADD);
        List<ColMetadata> updateCols = compareResult.get(ColumnDesignCompareConstant.TYPE_UPDATE);
        List<ColMetadata> delCols = compareResult.get(ColumnDesignCompareConstant.TYPE_DEL);
        List<ColMetadata> commentCols = compareResult.get(ColumnDesignCompareConstant.TYPE_COMMENT);
        log.info("新增:{}", JSONObject.toJSONString(addCols));
        log.info("修改:{}", JSONObject.toJSONString(updateCols));
        log.info("删除:{}", JSONObject.toJSONString(delCols));
        log.info("注释:{}", JSONObject.toJSONString(commentCols));
        //2.执行新增字段操作
        if (CollectionUtils.isNotEmpty(addCols)) {
            List<String> addColSqls = getRealSqlsByCols(tableDesign, addCols, new String[]{"ADD_COL_TPL.ftl"});
            log.info("addCols:{}", JSONObject.toJSONString(addColSqls));
            jdbcTemplate.batchUpdate(addColSqls.toArray(new String[0]));
        }
        //3.执行删除属性操作
        if (CollectionUtils.isNotEmpty(delCols)) {
            List<String> delColSqls = getRealSqlsByCols(tableDesign, delCols, new String[]{"DEL_COL_TPL.ftl"});
            log.info("delCols:{}", JSONObject.toJSONString(delCols));
            jdbcTemplate.batchUpdate(delColSqls.toArray(new String[0]));
        }
        //4.执行修改属性操作
        if (CollectionUtils.isNotEmpty(updateCols)) {
            List<String> updateSqls = getRealSqlsByCols(tableDesign, updateCols, new String[]{"UPDATE_1_ADD_TEMP_COL_TPL.ftl", "UPDATE_2_SET_COL_TPL.ftl", "DEL_COL_TPL.ftl", "RENAME_COL_TPL.ftl"});
            log.info("updateSqls:{}", JSONObject.toJSONString(updateSqls));
            jdbcTemplate.batchUpdate(updateSqls.toArray(new String[0]));
        }
        //5.设置备注
        if (CollectionUtils.isNotEmpty(commentCols)) {
            List<String> updateColWithComments = getCommentsByCols(tableDesign, commentCols, "COMMENT_ON_COL_TPL.ftl");
            log.info("commentsSqls:{}", JSONObject.toJSONString(updateColWithComments));
            jdbcTemplate.batchUpdate(updateColWithComments.toArray(new String[0]));
        }
        //6.清空元数据
        tableDefinitionV2Service.clearData(tableDesign.getTableName(), tableDesign.getTableId());
        //7.创建表列元数据
        tableDefinitionV2Service.initTableCols(tableDesign, columnDesigns);
        return true;
    }

    private List<String> getCommentsByCols(TableDesign tableDesign, List<ColMetadata> cols, String tpl) {
        List<String> sqls = new ArrayList<>();
        cols.forEach(col -> {
            sqls.add(getCommentsByCol(tableDesign, col, tpl));
        });
        return sqls;
    }

    private String getCommentsByCol(TableDesign tableDesign, ColMetadata col, String tpl) {
        Map<String, Object> colContext = new HashMap<>();
        colContext.put("col", col);
        colContext.put("tableName", tableDesign.getTableEN());
        String sql = getSqlTpl(TPL_TYPE, tpl, colContext);
        return sql;
    }


    private List<String> getRealSqlsByCols(TableDesign tableDesign, List<ColMetadata> cols, String... tpls) {
        List<String> sqls = new ArrayList<>();
        cols.forEach(col -> {
            sqls.addAll(getRealSqlsByCol(tableDesign, col, tpls));
        });
        return sqls;
    }

    private List<String> getRealSqlsByCol(TableDesign tableDesign, ColMetadata col, String... tpls) {
        List<String> sqls = new ArrayList<>();
        Map<String, Object> colContext = new HashMap<>();
        colContext.put("col", col);
        colContext.put("tableName", tableDesign.getTableEN());
        for (String tpl : tpls) {
            String sql = getSqlTpl(TPL_TYPE, tpl, colContext);
            sqls.add(sql);
        }
        return sqls;
    }


    private String getSqlTpl(String templateLoadingPath, String tpl, Map<String, Object> context) {
        try {
            return FreemarkerUtils.generateString(templateLoadingPath, tpl, context);
        } catch (Exception e) {
            log.error("getSqlTpl error", e);
        }
        return "";
    }

}